﻿

CREATE PROCEDURE [dbo].[GenNotaContabilaUnion]
	-- Add the parameters for the stored procedure here
	@cont TipCont,
	@CodUnitate smallint,
	@DeLaData datetime,
	@PanaLaData datetime,
	@An smallint,
	@strFilter varchar(2000),
	@strFilter2 varchar(2000),
	@strOrder varchar(1000),
	@strFiltruNrNota varchar(2000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @cmd varchar(5000)
	DECLARE @strCodUnitate varchar(20)
	SET @strCodUnitate = cast(@codUnitate as varchar(20))
	DECLARE @DtDataStartUI varchar(40)
	DECLARE @DtDataEndUI varchar(40)

	SET @DtDataStartUI = cast(YEAR(@DeLaData) as varchar(4)) + '-' +cast(MONTH(@DeLaData) as varchar(2)) + '-' + cast(DAY(@DeLaData) as varchar(2))
	SET @DtDataEndUI = cast(YEAR(@PanaLaData) as varchar(4)) + '-' +cast(MONTH(@PanaLaData) as varchar(2)) + '-' + cast(DAY(@PanaLaData) as varchar(2))

    -- Insert statements for procedure here
	SET @cmd = 'SELECT b.NumarDoc, b.DataDoc, b.NumarNota, a.ContDebit, a.ContCredit, 
                      b.IDJurnal, a.ValoareLei, a.DataDocJust, a.TipDocJust, a.NumarDocJust, a.ExplicatiiDocJust, 
                      b.TipFormula, c.Categorie, ''D'' AS Tip, a.CodUnitate
				FROM  [Rulaje' 
				IF @An <> 0 
					  SET @cmd = @cmd + cast(@an as varchar(4))
				SET @cmd = @cmd + '] a INNER JOIN  [RulajeDoc'
				IF @An <> 0 
					  SET @cmd = @cmd + cast(@an as varchar(4))                      
				SET @cmd = @cmd + '] b ON a.IDDocument = b.IDDocument INNER JOIN  [Conturi'
				IF @An <> 0 
					  SET @cmd = @cmd + cast(@an as varchar(4))                      
				SET @cmd = @cmd + '] c ON a.ContDebit = c.Cont AND c.CodUnitate = a.CodUnitate
				WHERE [ContDebit]<>''N/A'' AND (a.CodUnitate = ' + @strCodUnitate + ') ' 
				IF @cont<> ''
					SET @cmd = @cmd + ' AND (a.ContDebit like ''' + @cont + '%'' OR a.ContCredit like ''' + @cont + '%'')'

				SET @cmd = @cmd + ' AND (b.DataDoc >=''' +	@DtDataStartUI + ''' AND b.DataDoc<='''	 +  @DtDataEndUI + ''')'

				IF @strFilter <> ''
					SET @cmd = @cmd + @strFilter

				IF @strFiltruNrNota	<> ''
					SET @cmd = @cmd + ' AND b.NumarNota not in (' + @strFiltruNrNota + ') '

--				IF @strOrder = ''
--					SET @cmd = @cmd + 'ORDER BY b.DataDoc'
--				ELSE
--					SET @cmd = @cmd + @strOrder
		SET @cmd = @cmd + '  UNION SELECT b.NumarDoc, b.DataDoc, b.NumarNota, a.[ContCredit] AS [ContDebit], a.[ContDebit] AS ContCredit, 
                      b.IDJurnal, a.ValoareLei, a.DataDocJust, a.TipDocJust, a.NumarDocJust, a.ExplicatiiDocJust, 
                      b.TipFormula, c.Categorie, ''C'' AS Tip, a.CodUnitate
				FROM  [Rulaje' 
				IF @An <> 0 
					  SET @cmd = @cmd + cast(@an as varchar(4))
				SET @cmd = @cmd + '] a INNER JOIN  [RulajeDoc'
				IF @An <> 0 
					  SET @cmd = @cmd + cast(@an as varchar(4))                      
				SET @cmd = @cmd + '] b ON a.IDDocument = b.IDDocument INNER JOIN  [Conturi'
				IF @An <> 0 
					  SET @cmd = @cmd + cast(@an as varchar(4))                      
				SET @cmd = @cmd + '] c ON a.ContDebit = c.Cont AND c.CodUnitate = a.CodUnitate
				WHERE [ContCredit]<>''N/A'' AND (a.CodUnitate = ' + @strCodUnitate + ') ' 
				IF @cont<> ''
					SET @cmd = @cmd + ' AND (a.ContDebit like ''' + @cont + '%'' OR a.ContCredit like ''' + @cont + '%'')'

				SET @cmd = @cmd + ' AND (b.DataDoc >=''' +	@DtDataStartUI + ''' AND b.DataDoc<='''	 +  @DtDataEndUI + ''')'

				IF @strFilter2 <> ''
					SET @cmd = @cmd + @strFilter2
					
				IF @strFiltruNrNota	<> ''
					SET @cmd = @cmd + ' AND b.NumarNota NOT in (' + @strFiltruNrNota + ') '
					
				IF @strOrder = ''
					SET @cmd = @cmd + 'ORDER BY b.DataDoc'
				ELSE
					SET @cmd = @cmd + @strOrder
    PRINT(@cmd)		
	EXEC(@Cmd) 
	IF @@ERROR != 0 GOTO EROARE	





	GOTO Gata
	EROARE:
		RAISERROR ('Eroare la generarea datelor pentru [NotaContabilaView]!',16,-1)
		GOTO Gata
	Gata:

END